﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using NPOI.XSSF.Model;
using XiAnAirport.BUS;
using XiAnAirport.Models;

namespace XiAnAirport.DAL
{
    public class SpareDAL
    {
        public string AddSpare(SpareInfo spareInfo)
        {
            string strSql = string.Format("INSERT INTO spare (spareNo,spareName,spareCompany,spareModel,productionDate,contactPerson,phone,thresholdValue,alarmThresholdValue,content,sparePic,creationDate,creationPerson,liftTime)VALUES('{0}','{1}','{2}','{3}',{4},'{5}','{6}',{7},{8},'{9}','{10}','{11}','{12}',{13})"
                , spareInfo.spareNo, spareInfo.spareName, spareInfo.spareCompany, spareInfo.spareModel, spareInfo.productionDate, spareInfo.contactPerson, spareInfo.phone, spareInfo.thresholdValue, spareInfo.alarmThresholdValue, spareInfo.content, spareInfo.sparePic, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), spareInfo.creationPerson, spareInfo.liftTime);
            try
            {
                int count = SqlHelper<SpareInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string EditSpare(SpareInfo spareInfo)
        {
            string strSql = string.Format("UPDATE spare SET spareNo='{0}',spareName='{1}',spareCompany='{2}',spareModel='{3}',productionDate={4},contactPerson='{5}',phone='{6}',thresholdValue={7},alarmThresholdValue={8},content='{9}',sparePic='{10}',liftTime={12} WHERE id={11}"
                , spareInfo.spareNo, spareInfo.spareName, spareInfo.spareCompany, spareInfo.spareModel, spareInfo.productionDate, spareInfo.contactPerson, spareInfo.phone, spareInfo.thresholdValue, spareInfo.alarmThresholdValue, spareInfo.content, spareInfo.sparePic, spareInfo.id, spareInfo.liftTime);
            try
            {
                if (SqlHelper<SpareInfo>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string DelSpare(int spareid)
        {
            string strSql = string.Format("delete FROM spare WHERE id={0}", spareid);
            try
            {
                int count = SqlHelper<SpareInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string BatchDelSpare(string ids)
        {
            string strSql = string.Format("delete FROM spare WHERE id in ({0})", ids);
            try
            {
                int count = SqlHelper<SpareInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<SpareInfo> QuerySpare(SpareInfo spareInfo)
        {
            try
            {
                List<SpareInfo> list = new List<SpareInfo>();
                string strSql = "SELECT * FROM spare";
                string sqlwhere = string.Empty;
                if (!string.IsNullOrEmpty(spareInfo.spareNo))
                {
                    sqlwhere += " spareNo like @spareNo AND";
                }
                if (!string.IsNullOrEmpty(spareInfo.spareName))
                {
                    sqlwhere += " spareName like @spareName AND";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }
                if (spareInfo.Recordhead > -1 && spareInfo.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + spareInfo.spareName + "%";
                    string like_2 = "%" + spareInfo.spareNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@spareName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@spareNo", $"%{like_2}%");
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", spareInfo.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", spareInfo.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<SpareInfo>(dataTable);
                }
                return list;
            }
            catch
            {
                return null;
            }
        }

        public int QuerySpareConut(SpareInfo spareInfo)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM spare";
                string sqlwhere = string.Empty;
                if (!string.IsNullOrEmpty(spareInfo.spareNo))
                {
                    sqlwhere += " spareNo like @spareNo AND";
                }
                if (!string.IsNullOrEmpty(spareInfo.spareName))
                {
                    sqlwhere += " spareName like @spareName AND";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    string likename = "%" + spareInfo.spareName + "%";
                    string like_2 = "%" + spareInfo.spareNo + "%";
                    mySqlCommand.Parameters.AddWithValue("@spareName", $"%{likename}%");
                    mySqlCommand.Parameters.AddWithValue("@spareNo", $"%{like_2}%"); MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }

            return count;
        }

        public SpareInfo LoadSpare(int spareid)
        {
            string strSql = string.Format("SELECT * FROM spare WHERE id={0}", spareid);
            return SqlHelper<SpareInfo>.SqlSingleData(strSql);
        }

        public string CleanUpSpare()
        {
            string strSql = string.Format("delete FROM spare");
            try
            {
                int count = SqlHelper<SpareInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string AddSpareRecord(Spareupdaterecord spareupdaterecord)
        {
            string strSql = string.Format("INSERT INTO spareupdaterecord (spareId,updateDate,updateReason,updatePerson,creationDate,creationPerson,elevatorId,oldspareId,amount)VALUES({0},{1},'{2}','{3}','{4}','{5}',{6},{7},{8})"
                , spareupdaterecord.spareId, spareupdaterecord.updateDate, spareupdaterecord.updateReason, spareupdaterecord.updatePerson, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), spareupdaterecord.creationPerson, spareupdaterecord.elevatorId, spareupdaterecord.oldspareId, spareupdaterecord.amount);
            try
            {
                int count = SqlHelper<Spareupdaterecord>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                LogHelper.Log(e.ToString());
                return "fail";
            }
        }

        public string EditSpareRecord(Spareupdaterecord spareupdaterecord)
        {
            string strSql = string.Format("UPDATE spareupdaterecord SET spareId={0},updateDate={1},updateReason='{2}',updatePerson='{3}',elevatorId={5} WHERE id={4}"
                , spareupdaterecord.spareId, spareupdaterecord.updateDate, spareupdaterecord.updateReason, spareupdaterecord.updatePerson, spareupdaterecord.id, spareupdaterecord.elevatorId);
            try
            {
                if (SqlHelper<Spareupdaterecord>.SqlExecute(strSql))
                {
                    return "success";
                }
                else
                {
                    return "fail";
                }
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string DelSpareRecord(int spareid)
        {
            string strSql = string.Format("delete FROM spareupdaterecord WHERE id={0}", spareid);
            try
            {
                int count = SqlHelper<Spareupdaterecord>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string BatchDelSpareRecord(string ids)
        {
            string strSql = string.Format("delete FROM spareupdaterecord WHERE id in ({0})", ids);
            try
            {
                int count = SqlHelper<Spareupdaterecord>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<Spareupdaterecord> QuerySpareRecord(Spareupdaterecord spareupdaterecord)
        {
            try
            {
                List<Spareupdaterecord> list = new List<Spareupdaterecord>();

                string strSql = "SELECT vi_sparerecord.*,spare.spareName AS oldSpareName,spare.spareModel as oldSpareModel FROM vi_sparerecord LEFT JOIN spare ON vi_sparerecord.oldspareId=spare.id";
                string sqlwhere = string.Empty;

                if (spareupdaterecord.elevatorId > 0)
                {
                    sqlwhere += " vi_sparerecord.elevatorId=@elevatorId AND";
                }
                if (!string.IsNullOrEmpty(spareupdaterecord.startTime))
                {
                    strSql += " AND DATE(vi_sparerecord.updateDate)>=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(spareupdaterecord.endTime))
                {
                    strSql += " AND DATE(vi_sparerecord.updateDate) <=DATE(@endTime)";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }
                if (spareupdaterecord.Recordhead > -1 && spareupdaterecord.Recordtail > 0)
                {
                    strSql += " LIMIT @Recordhead,@Recordtail";
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@elevatorId", spareupdaterecord.elevatorId);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(spareupdaterecord.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(spareupdaterecord.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@Recordhead", spareupdaterecord.Recordhead);
                    mySqlCommand.Parameters.AddWithValue("@Recordtail", spareupdaterecord.Recordtail);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<Spareupdaterecord>(dataTable);
                }
                return list;

            }
            catch
            {
                return null;
            }
        }

        public int QuerySpareRecordConut(Spareupdaterecord spareupdaterecord)
        {
            int count = 0;
            try
            {
                string strSql = "SELECT COUNT(1) FROM vi_sparerecord";
                string sqlwhere = string.Empty;
                if (spareupdaterecord.elevatorId > 0)
                {
                    sqlwhere += " vi_sparerecord.elevatorId=@elevatorId AND";
                }
                if (!string.IsNullOrEmpty(spareupdaterecord.startTime))
                {
                    strSql += " AND DATE(vi_sparerecord.updateDate)>=DATE(@startTime)";
                }
                if (!string.IsNullOrEmpty(spareupdaterecord.endTime))
                {
                    strSql += " AND DATE(vi_sparerecord.updateDate) <=DATE(@endTime)";
                }

                if (sqlwhere.Length > 0)
                {
                    strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4); ;
                }

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@elevatorId", spareupdaterecord.elevatorId);
                    mySqlCommand.Parameters.AddWithValue("@startTime", Convert.ToDateTime(spareupdaterecord.startTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    mySqlCommand.Parameters.AddWithValue("@endTime", Convert.ToDateTime(spareupdaterecord.endTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    count = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]);
                }
            }
            catch
            {

            }

            return count;
        }

        public List<Spareupdaterecord> QuerySpareRecordByIds(string ids)
        {
            string strSql = "SELECT vi_sparerecord.*,spare.spareName AS oldSpareName,spare.spareModel as oldSpareModel FROM vi_sparerecord LEFT JOIN spare ON vi_sparerecord.oldspareId=spare.id where vi_sparerecord.id in(" + ids + ")";

            return SqlHelper<Spareupdaterecord>.SqlList(strSql);
        }

        public Spareupdaterecord LoadSpareRecord(int spareupdaterecordid)
        {
            string strSql = string.Format("SELECT * FROM vi_sparerecord WHERE id={0}", spareupdaterecordid);
            return SqlHelper<Spareupdaterecord>.SqlSingleData(strSql);
        }

        public string CleanUpSpareRecord()
        {
            string strSql = string.Format("delete FROM spareupdaterecord");
            try
            {
                int count = SqlHelper<SpareInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string AddSpareElevator(SpareElevator spareElevator)
        {
            string strSql = string.Format("INSERT INTO spare_elevator (spareId,elevatorId)VALUES({0},{1})"
                , spareElevator.spareId, spareElevator.elevatorId);
            try
            {
                int count = SqlHelper<SpareElevator>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                LogHelper.Log(e.ToString());
                return "fail";
            }
        }

        public bool CheckSpareElevator(SpareElevator spareElevator)
        {
            string strSql1 = "SELECT COUNT(1) FROM spare_elevator WHERE elevatorId=" + spareElevator.elevatorId + " AND spareId=" + spareElevator.spareId;

            try
            {
                int count = SqlHelper<SpareElevator>.SqlScalar(strSql1);
                if (count > 0)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                throw e;
                return false;
            }
            return false;
        }

        public string BatchDelSpareElevator(string ids)
        {
            string strSql = string.Format("delete FROM spare_elevator WHERE id in ({0})", ids);
            try
            {
                int count = SqlHelper<SpareInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public string DelSpareElevator(int id)
        {
            string strSql = string.Format("delete FROM spare_elevator WHERE id={0}", id);
            try
            {
                int count = SqlHelper<SpareInfo>.SqlExecuteRows(strSql);

                if (count > 0)
                {
                    return "success";
                }
                return "fail";
            }
            catch (Exception e)
            {
                return "fail," + e.ToString();
            }
        }

        public List<SpareElevator> QuerySpareElevator(int elevatorid)
        {
            try
            {
                List<SpareElevator> list = new List<SpareElevator>();
                string strSql = "SELECT * FROM vi_spareelevator WHERE elevatorId=@elevatorid";

                using (MySqlConnection connection = DataFactory.GetMysqlConnection())
                {
                    MySqlCommand mySqlCommand = new MySqlCommand(strSql, connection);

                    // 添加参数
                    mySqlCommand.Parameters.AddWithValue("@elevatorid", elevatorid);
                    MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCommand);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    list = ToList<SpareElevator>(dataTable);
                }
                return list;
            }
            catch
            {
                return null;
            }
        }

        public List<T> ToList<T>(DataTable dt) where T : class, new()
        {
            List<T> list = new List<T>();
            //确认参数有效,若无效则返回Null
            if (dt == null)
                return list;
            else if (dt.Rows.Count == 0)
                return list;
            Dictionary<string, string> dicField = new Dictionary<string, string>();
            Dictionary<string, string> dicProperty = new Dictionary<string, string>();
            Type type = typeof(T);

            //创建字段字典，方便查找字段名
            foreach (var item in type.GetFields())
            {
                dicField.Add(item.Name.ToLower(), item.Name);
            }
            //创建属性字典，方便查找属性名
            foreach (var item in type.GetProperties())
            {
                dicProperty.Add(item.Name.ToLower(), item.Name);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //创建泛型对象
                T _t = Activator.CreateInstance<T>();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string memberKey = dt.Columns[j].ColumnName.ToLower();

                    //字段赋值
                    if (dicField.ContainsKey(memberKey))
                    {
                        FieldInfo theField = type.GetField(dicField[memberKey]);
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theField.FieldType;
                            //dbValue = dbValue.ChangeType(memberType);
                            dbValue = Convert.ChangeType(dbValue, memberType);
                        }
                        theField.SetValue(_t, dbValue);
                    }
                    //属性赋值
                    if (dicProperty.ContainsKey(memberKey))
                    {
                        PropertyInfo theProperty = type.GetProperty(dicProperty[memberKey]);
                        if (!theProperty.CanWrite) continue;
                        var dbValue = dt.Rows[i][j];
                        if (dbValue.GetType() == typeof(DBNull))
                            dbValue = null;
                        if (dbValue != null)
                        {
                            Type memberType = theProperty.PropertyType;
                            if (memberType.IsGenericType && memberType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                            {
                                NullableConverter newNullableConverter = new NullableConverter(memberType);
                                dbValue = newNullableConverter.ConvertFrom(dbValue);
                            }
                            else
                            {
                                dbValue = Convert.ChangeType(dbValue, memberType);
                            }
                        }
                        theProperty.SetValue(_t, dbValue, null);
                    }
                }
                list.Add(_t);
            }
            return list;
        }
    }
}
